package ms.common.util.excel;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import ms.common.util.string.StringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Project Name: ms
 * File Name: ExcelUtils
 * Package Name: ms.common.util.excel
 * Date: 2018/6/8  15:22
 * Copyright (c) 2018, tianyul All Rights Reserved.
 */
public class ExcelClient<T> {
    private Class<T> entityClass;
    private final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 解析excel--导入
     * @param entityClass
     * @param inputStream
     * @param fileName
     * @return
     */
    public List<T> parseExcel(Class<T> entityClass, InputStream inputStream, String fileName) {
        this.entityClass = entityClass;
        List<T> result = new ArrayList<>();

        CharSequence cs = "E";

        Workbook workbook = getWorkBook(inputStream, fileName);

        if(workbook == null) {
            return null;
        }
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getLastRowNum();
        System.out.println("一共有--------------->" + (rows) + "<<条记录");
        if (rows > 0) {
            int realCellNum = 0;
            for (int r = 1; r < rows + 1; r++) {//第一行是表头
                //封装正好信息
                Map<Integer,Object> map = new HashMap<>();
                //获得当前行信息
                Row row = sheet.getRow(r);
                if(row == null) {
                    break;
                }
                for (int clo = 0; clo < row.getLastCellNum(); clo++) {
                    int cellNum = row.getLastCellNum();

                    if(cellNum >= 1000) {//少于一千个字段   这个是最大列数
                        cellNum = realCellNum;
                    } else {
                        realCellNum = cellNum;
                    }

                    for(int j = 0; j < cellNum; j++) {
                        //得到当前行数据
                        Cell cell = row.getCell(j);
                        if(cell!=null){
                            switch(cell.getCellTypeEnum()){
                                case STRING:
                                    map.put(j, cell.getRichStringCellValue().toString().replaceAll("\n", ""));//目前有的数据存在换行，这里强制转换
                                    break;
                                case NUMERIC:
                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        Date date = cell.getDateCellValue();
                                        map.put(j, sdf.format(date));
                                    } else if(cell.getNumericCellValue() == new BigDecimal(cell.getNumericCellValue()).setScale(0, BigDecimal.ROUND_UP).intValue()) {
                                        map.put(j, new BigDecimal(cell.getNumericCellValue()).setScale(0).intValue());
                                    } else if(String.valueOf(cell.getNumericCellValue()).contains(cs)){//科学计数法
                                        DecimalFormat df = new DecimalFormat("0");
                                        map.put(j, df.format(cell.getNumericCellValue()));
                                    } else {
                                        map.put(j, cell.getNumericCellValue());
                                    }
                                    break;
                                case FORMULA:
                                    map.put(j, cell.getCellFormula());
                                    break;
                            }
                        } else {
                            map.put(j, null);
                            continue;
                        }
                    }
                }
                result.add(setT(map));
            }
        }
        return result;
    }

    /**
     * 将数据转换成工作簿
     * @param list
     * @return
     */
    public HSSFWorkbook beanToWorkbook(List<T> list, String sheetName, List<String> title, HSSFCellStyle firstStyle, HSSFCellStyle style) {
        //构造一个返回对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建Excel工作表对象
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //创建第一行
        HSSFRow firstRow = sheet.createRow((short)0); //创建Excel工作表的行
        HSSFCellStyle firstCellStyle;//创建单元格样式
        if(null == firstStyle) {
            firstCellStyle = workbook.createCellStyle();
        } else {
            firstCellStyle = firstStyle;
        }
        firstRow.createCell((short)0).setCellStyle(firstCellStyle); //创建Excel工作表指定行的单元格

        for (int i = 0; i < title.size(); i++) {
            firstRow.createCell((short)i).setCellValue(title.get(i)); //设置Excel工作表的值
        }

        //没有数据，返回表头
        if(StringUtil.isEmpty(list)) {
            return workbook;
        }

        HSSFCellStyle cellStyle;
        if(null == style) {
            cellStyle = workbook.createCellStyle();//创建单元格样式
        } else {
            cellStyle = style;
        }

        //数据渲染
        for (int i = 0; i < list.size(); i++) {
            T t = list.get(i);
            //获得row
            setCell(t, cellStyle, i + 1, sheet);
        }

        return workbook;
    }

    /**
     * 将excel的连接转换成对象数组，url必须以 xls 或者 xlsx 结尾
     * @param url
     * @return
     */
    public List<T> urlToObject(Class<T> entityClass, String url) {
        this.entityClass = entityClass;
        File file = new File(url);
        String fileName = file.getName();
        try {
            InputStream inputStream = new FileInputStream(file);
            return this.parseExcel(entityClass, inputStream, fileName);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return null;
    }

    private void setCell(T t, HSSFCellStyle cellStyle, Integer num, HSSFSheet sheet) {
        HSSFRow row = sheet.createRow(num);

        Class clazz = t.getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            //获得属性名
            String properties = fields[i].getName();
            //获得方法实体
            try {
                PropertyDescriptor descriptor = new PropertyDescriptor(properties, clazz);
                Method readMethod = descriptor.getReadMethod();
                //强行转换成字符串
                String object = readMethod.invoke(t) + "";
                //设置Excel工作表的值
                row.createCell((short)i).setCellStyle(cellStyle);
                row.createCell((short)i).setCellValue(object);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } catch (IntrospectionException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 将map转换成指定类型的对象
     * @param map
     * @return
     */
    private T setT(Map<Integer,Object> map) {
        try {
            T obj = entityClass.newInstance();
            Class clazz = obj.getClass();
            Field[] fields = clazz.getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                if(!StringUtil.isEmpty(map.get(i))) {
                    //获得属性名
                    String properties = fields[i].getName();
                    //获得方法名
                    String methodName = "set" + properties.substring(0, 1).toUpperCase() + properties.substring(1, properties.length());
                    //获得类型
                    Class typeClass = fields[i].getType();
                    //获得方法实体
                    Method method = clazz.getMethod(methodName, new Class[]{typeClass});
                    if (typeClass == String.class) {

                        method.invoke(obj, new Object[] { map.get(i).toString().trim() });
                    } else if (typeClass == Integer.class) {

                        method.invoke(obj, new Object[] { new BigDecimal(map.get(i).toString().trim()).setScale(0).intValue() });
                    } else if (typeClass == Long.class) {

                        method.invoke(obj, new Object[] { new BigDecimal(map.get(i).toString().trim()).setScale(0).longValue() });
                    } else if (typeClass == Double.class) {

                        method.invoke(obj, new Object[] { new BigDecimal(map.get(i).toString().trim()).doubleValue() });
                    } else if (typeClass == Float.class) {

                        method.invoke(obj, new Object[] { Float.parseFloat(map.get(i).toString().trim()) });
                    } else if (typeClass == Date.class) {

                        method.invoke(obj, new Object[] { map.get(i) });
                    }
                } else {
                    //获得属性名
                    String properties = fields[i].getName();
                    //获得方法名
                    String methodName = "set" + properties.substring(0, 1).toUpperCase() + properties.substring(1, properties.length());
                    //获得类型
                    Class typeClass = fields[i].getType();
                    //获得方法实体
                    Method method = clazz.getMethod(methodName, new Class[]{typeClass});
                    Object value = new Object();
                    if (typeClass == String.class) {
                        value = "";
                    } else if (typeClass == Integer.class) {

                        value = 0;
                    } else if (typeClass == Long.class) {

                        value = 0L;
                    } else if (typeClass == Double.class) {

                        value = 0d;
                    } else if (typeClass == Float.class) {

                        value = 0f;
                    } else if (typeClass == Date.class) {

                        value = new Date();
                    }
                    method.invoke(obj, new Object[] { value });
                }
            }
            return obj;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获得Workbook工作薄对象
     * @param inputStream
     * @param fileName
     * @return
     */
    private static Workbook getWorkBook(InputStream inputStream, String fileName) {
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(inputStream);
            } else if(fileName.endsWith("xlsx")) {
                //2007
                workbook = new XSSFWorkbook(inputStream);
            }  else {
                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }
}
